﻿using SpreadsheetGear;
using Sustainalytics.ClientDW.DataAccessLayer;
using Sustainalytics.ClientDW.Entities.Universes;
using Sustainalytics.DataLayer;
using Sustainalytics.DataLayer.Exceptions.AccountsAndUsers;
using Sustainalytics.Entities;
using Sustainalytics.Utils.Exceptions.Universes;
using Sustainalytics.Auth.Claims;
using System;
using System.Collections.Generic;
using System.IO;
using System.Collections.Concurrent;
using System.Linq;
using System.Diagnostics;
using Sustainalytics.Synchronization.Interface;
using Sustainalytics.DataLayer.Shared;
using Sustainalytics.ClientDW.Entities;

namespace Sustainalytics.Universes.Synchronization
{
    public class UniverseSynchronization : IUniverseSynchronization
    {
        readonly IUniversesRepository _universeRepository = null;
        readonly IAccountsRepository _accountsRepository = null;
        readonly IClientDwRepository _clientDwRepository = null;
        private readonly ConcurrentDictionary<KeyValuePair<string,Guid>, int?> _universesCache = new ConcurrentDictionary<KeyValuePair<string,Guid>, int?>();

        public const string UniversesWorksheetName = "Universes";
        public const string CompaniesInUniversesWorksheetName = "CompaniesInUniverses";
        
        public static IUniverseSynchronization GetInstance(
            IUniversesRepository universesRepository,
            IAccountsRepository accountsRepository,
            IClientDwRepository clientDwRepository)
        {
            return new UniverseSynchronization(universesRepository, accountsRepository, clientDwRepository);
        }

        private UniverseSynchronization(IUniversesRepository universesRepository,
            IAccountsRepository accountsRepository,
            IClientDwRepository clientDwRepository)
        {
            _universeRepository = universesRepository;
            _accountsRepository = accountsRepository;
            _clientDwRepository =  clientDwRepository;
        }

        public bool SyncUniverses(Sustainalytics.Synchronization.Interface.SyncContext syncContext)
        {

            bool hasChanges = false;

            Guid fileId = Guid.Empty;
            try
            {
                UploadLog uploadLog = _universeRepository.GetUploadLog();
                if (uploadLog == null)
                {
                    syncContext.Log("There is no universe file uploaded for processing.");
                    return hasChanges; //no processing needed.
                }
                if (uploadLog.Processed)
                {
                    syncContext.Log(string.Format("The file uploaded on: {0} has already been processed. No processing needed.", uploadLog.Created.ToString("dd/MM/yyyy HH:mm")));
                    return hasChanges; //no processing needed.
                }

                fileId = uploadLog.FileId;
                byte[] uploadedFile = _universeRepository.GetFile(uploadLog.FileId);

                var universesWorksheet = Factory.GetWorkbookSet().Workbooks.OpenFromMemory(uploadedFile).Worksheets[UniversesWorksheetName];
                if (universesWorksheet == null)
                {
                    syncContext.Log(string.Format("The file uploaded on: {0} does not contain a worksheet named 'Universes'.", uploadLog.Created.ToString("dd/MM/yyyy HH:mm")));
                    throw new WorksheetMissingException("The worksheet could not be found", "Universes");
                }

                var companiesInUniversesWorksheet = Factory.GetWorkbookSet().Workbooks.OpenFromMemory(uploadedFile).Worksheets[CompaniesInUniversesWorksheetName];
                if (companiesInUniversesWorksheet == null)
                {
                    syncContext.Log(string.Format("The file uploaded on: {0} does not contain a worksheet named 'CompaniesInUniverses'.", uploadLog.Created.ToString("dd/MM/yyyy HH:mm")));
                    throw new WorksheetMissingException("The worksheet could not be found", "CompaniesInUniverses");
                }

                var accounts = _accountsRepository.GetAccountsByds(null);

                var processUniverseHasChanges = ProcessUniversesSheet(universesWorksheet, syncContext, fileId, accounts);
                var processCompaniesInUniverseHasChanges = ProcessCompaniesInUniversesWorksheet(companiesInUniversesWorksheet, syncContext, fileId, 
                    accounts, syncContext.GAConnectionStrings["ClientDW"]);

                if (processUniverseHasChanges || processCompaniesInUniverseHasChanges)
                    hasChanges = true;

                //TODO add inside the current changes
                syncContext.AddChange(new ChangedElement()
                {
                    AffectedCollection = uploadLog.GetType().Name,
                    AffectedDB = syncContext.GAConnectionStrings["ClientUniverses"],
                    AffectedDocument = uploadLog,
                    AffectedDocumentId = uploadLog.Id,
                    AffectedObjectName = ChangedElement.AFFECTED_INPUT_FILE_NAME_OBJECT,
                    AffectedDocumentType = uploadLog.GetType()
                });

                syncContext.Log(string.Format("The file uploaded on: {0} has been successfully processed.", uploadLog.Created.ToString("dd/MM/yyyy HH:mm")));
                return hasChanges;
            }
            catch (WorksheetMissingException ex)
            {
                _universeRepository.AddUploadErrorLog(new UploadErrorLog() { RowNumber = 0, ErrorMessage = ex.Message, Worksheet = ex.WorksheetName, FileId = fileId });
                return hasChanges;
            }
            catch (FileNotFoundException ex)
            {
                _universeRepository.AddUploadErrorLog(new UploadErrorLog() { RowNumber = 0, ErrorMessage = ex.Message, FileId = fileId });
                return hasChanges;
            }
            catch (Exception)
            {
                _universeRepository.AddUploadErrorLog(new UploadErrorLog() { RowNumber = 0, ErrorMessage = "Critical error has been encountered. Please contact ICT for further assistance.", Worksheet = "", FileId = fileId });
                return hasChanges;
            }
        }

        private bool ProcessUniversesSheet(IWorksheet universesWorksheet, SyncContext syncReports, Guid fileId, List<Account> accounts)
        {
            bool hasChanges = false;
            List<Guid> emptiedAccounts = new List<Guid>();
            var universesCells = universesWorksheet.Cells;

            var headerUniv = universesCells[0, (int)UniversesIndices.UniverseOne].Text.Trim();
            if (String.IsNullOrEmpty(headerUniv))
            {
                syncReports.Log(String.Format("Invalid Excel format for {0} sheet: it should have the following mandatory columns: feature, account name, account id, universes!", UniversesWorksheetName));
                return false;
            }

            for (int rowNumber = 1; /* Break condition is evaluated inside */; rowNumber++)
            {
                try
                {
                    var accountName = universesCells[rowNumber, (int)UniversesIndices.AccountName].Text.Trim();
                    var featureName = universesCells[rowNumber, (int)UniversesIndices.FeatureIndex].Text.Trim();
                    var accountIdString = universesCells[rowNumber, (int)UniversesIndices.AccountId].Text.Trim();

                    //If all cells are empty end the sync process
                    if (string.IsNullOrWhiteSpace(accountName) && string.IsNullOrWhiteSpace(featureName) && string.IsNullOrWhiteSpace(accountIdString))
                    {
                        syncReports.Log(string.Format("Successfully processed '{0}' rows from the Universes worksheet.", rowNumber));
                        break;
                    }
                    //If there is at least 1 empty cell go to next line
                    if (string.IsNullOrWhiteSpace(accountName) || string.IsNullOrWhiteSpace(featureName) || string.IsNullOrWhiteSpace(accountIdString))
                        continue;

                    var accountId = GetAccountId(rowNumber, accountIdString, true);

                    CheckMatchBetweenAccountNameAndID(rowNumber, accountName, accountId, accounts, true);

                    //Empty universes/feature universes/company universes for accounts in the excel
                    if (!emptiedAccounts.Contains(accountId))
                    {
                        _universeRepository.DeleteFeatureUniversesByAccountId(accountId);
                        _universeRepository.DeleteUniversesByAccountId(accountId);

                        emptiedAccounts.Add(accountId);
                    }

                    var feature = this.GetAccountFeatureByString(featureName, rowNumber);

                    var universeOneName = universesCells[rowNumber, (int)UniversesIndices.UniverseOne].Text.Trim();
                    var universeTwoName = universesCells[rowNumber, (int)UniversesIndices.UniverseTwo].Text.Trim();
                    var universeThreeName = universesCells[rowNumber, (int)UniversesIndices.UniverseThree].Text.Trim();
                    var universeFourName = universesCells[rowNumber, (int)UniversesIndices.UniverseFour].Text.Trim();
                    var universeFiveName = universesCells[rowNumber, (int)UniversesIndices.UniverseFive].Text.Trim();
                    var universeSixName = universesCells[rowNumber, (int)UniversesIndices.UniverseSix].Text.Trim();

                    List<int> featureUniverseIds = new List<int>();
                    AddUniverse(fileId, rowNumber, accountName, accountId, universeOneName, featureUniverseIds);
                    AddUniverse(fileId, rowNumber, accountName, accountId, universeTwoName, featureUniverseIds);
                    AddUniverse(fileId, rowNumber, accountName, accountId, universeThreeName, featureUniverseIds);
                    AddUniverse(fileId, rowNumber, accountName, accountId, universeFourName, featureUniverseIds);
                    AddUniverse(fileId, rowNumber, accountName, accountId, universeFiveName, featureUniverseIds);
                    AddUniverse(fileId, rowNumber, accountName, accountId, universeSixName, featureUniverseIds);

                    var featureUniverse = _universeRepository.GetFeatureUniverse(accountId, feature);
                    if (featureUniverse != null)
                    {
                        _universeRepository.AddUploadErrorLog(new UploadErrorLog()
                        {
                            RowNumber = rowNumber + 1,
                            ErrorMessage = string.Format("The feature: '{0}' has already been processed for account: '{1}'.",
                                feature.ToString(), accountName),
                            Worksheet = "Universes",
                            FileId = fileId
                        });
                        continue;
                    }

                    featureUniverse = new FeatureUniverse() { AccountId = accountId, Feature = feature, UniverseIds = featureUniverseIds };
                    _universeRepository.AddFeatureUniverse(featureUniverse);

                    hasChanges = true;
                }
                catch (AccountFeatureNotFoundException ex)
                {
                    _universeRepository.AddUploadErrorLog(new UploadErrorLog() { RowNumber = ex.RowNumber + 1, ErrorMessage = ex.Message, Worksheet = "Universes", FileId = fileId });
                    continue;
                }
                catch (AccountNotFoundException ex)
                {
                    _universeRepository.AddUploadErrorLog(new UploadErrorLog() { RowNumber = ex.RowNumber + 1, ErrorMessage = ex.Message, Worksheet = "Universes", FileId = fileId });
                    continue;
                }
                catch (ApplicationException ex)
                {
                    _universeRepository.AddUploadErrorLog(new UploadErrorLog() { RowNumber = rowNumber + 1, ErrorMessage = ex.Message, Worksheet = CompaniesInUniversesWorksheetName, FileId = fileId });
                    syncReports.Log(ex.Message);
                    continue;
                }
                catch (Exception ex)
                {
                    _universeRepository.AddUploadErrorLog(new UploadErrorLog() { RowNumber = 0, ErrorMessage = string.Format("Unknown error. Please contact ICT support. Error message: {0}", ex.Message), Worksheet = "Universes", FileId = fileId });
                    continue;
                }
            }

            return hasChanges;
        }

        private void CheckMatchBetweenAccountNameAndID(int rowNumber, string accName, Guid id, List<Account> accounts, bool universesSheet)
        {
            var acc = accounts.FirstOrDefault(a => a.Id == id);
            if (acc == null)
            {
                throw new ApplicationException(String.Format("Cannot find in db the account Id {0} at line {1}, column {2} in sheet {3}!", id, ++rowNumber, 
                    (int)CompaniesInUniversesIndices.AccountId, universesSheet ? UniversesWorksheetName : CompaniesInUniversesWorksheetName));
            }

            if(acc.Name.Trim() != accName.Trim())
                throw new ApplicationException(String.Format("Account Name {0} doesn't match the Id {1} at line {2} in sheet {3} !", accName, id, ++rowNumber, 
                    universesSheet ? UniversesWorksheetName : CompaniesInUniversesWorksheetName));
        }


        private static Guid GetAccountId(int rowNumber, string accountIdString, bool universesSheet)
        {
            if (String.IsNullOrEmpty(accountIdString))
            {
                throw new ApplicationException(String.Format("Empty account Id at line {0}, column {1} in sheet {2} !", ++rowNumber, (int)CompaniesInUniversesIndices.AccountId, universesSheet ? UniversesWorksheetName : CompaniesInUniversesWorksheetName));
            }

            Guid accountId;
            if (!Guid.TryParse(accountIdString, out accountId))
            {
                throw new ApplicationException(String.Format("Invalid account Id {0} at line {1}, column {2} in sheet {3} !", accountIdString, ++rowNumber, (int)CompaniesInUniversesIndices.AccountId, universesSheet ? UniversesWorksheetName : CompaniesInUniversesWorksheetName));
            }
            return accountId;
        }

        private void AddUniverse(Guid fileId, int rowNumber, string accountName, Guid accountId, string universeName, List<int> featureUniverseIds)
        {
            if (!string.IsNullOrWhiteSpace(universeName))
            {
                var universe = _universeRepository.GetUniverse(universeName, accountId);
                if (universe == null)
                {
                    if (_universeRepository.GetUniversesByAccountId(accountId).Count == 6)
                    {
                        _universeRepository.AddUploadErrorLog(new UploadErrorLog()
                        {
                            RowNumber = rowNumber + 1,
                            ErrorMessage = string.Format("The max number of universes has been reached for the account with name: '{0}'.", accountName),
                            Worksheet = UniversesWorksheetName,
                            FileId = fileId
                        });
                    }
                    else
                    {
                        universe = CreateUniverse(universeName, accountId);
                        _universeRepository.AddUniverse(universe);
                    }
                }

                if (universe != null)
                    featureUniverseIds.Add(universe.Id);
            }
        }

        List<Guid> _companiesSheetAccountIds = null;
        private bool ProcessCompaniesInUniversesWorksheet(IWorksheet companiesInUniversesWorksheet, SyncContext syncReports, Guid fileId, List<Account> accounts, string clientDWConnString)
        {
            var universesCells = companiesInUniversesWorksheet.Cells;
            var headerUniv = universesCells[0, (int)CompaniesInUniversesIndices.LastColumn].Text.Trim();
            if (String.IsNullOrEmpty(headerUniv))
            {
                syncReports.Log(
                    $"Invalid Excel format for {CompaniesInUniversesWorksheetName} sheet: it should have the following mandatory columns: " +
                     "company name, capital IQ ID, account name, account ID, universe name!");
                return false;
            }

            var entitiesQuery = SheetToEntity(companiesInUniversesWorksheet, fileId, accounts, syncReports);

            if (_companiesSheetAccountIds != null && _companiesSheetAccountIds.Any())
            {
                this._universeRepository.DeleteUniverseCompanyByAccountIds(_companiesSheetAccountIds);
            }

            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Restart();

            var entities = entitiesQuery.ToList();
            syncReports.Log(string.Format("Successfully processed '{0}' companies, '{1}' with errors.", entities.Count(pair => pair.Key != null), entities.Count(pair => pair.Value != null)));

            var univCompanies = entities.Where(pair => pair.Key != null).Select(pair => pair.Key).ToList();

            this._universeRepository.InsertBatchUniverseCompany(ComputeExtendedUniversesForCompanies(univCompanies, clientDWConnString).ToList());
            this._universeRepository.AddUploadErrorBatchLog(entities.Where(pair => pair.Value != null).Select(pair => pair.Value).ToList());
            
            stopwatch.Stop();
            Console.WriteLine("save: Elapsed {0} ms", stopwatch.ElapsedMilliseconds);

            return entitiesQuery.Any();
        }

        private IEnumerable<UniverseCompany> ComputeExtendedUniversesForCompanies(IEnumerable<UniverseCompany> universeCompanies, string clientDWConnString)
        {
            var corporateDataList = new RepositoryCollection<CorporateData>(clientDWConnString).ReadAll(new string[] { "ResearchParentId", "CoverageIds" });

            return new UniverseResearchCoverage().ProcessCoverage(universeCompanies, corporateDataList);
        }


        private IEnumerable<KeyValuePair<UniverseCompany, UploadErrorLog>> SheetToEntity(IWorksheet companiesInUniversesWorksheet, Guid fileId, List<Account> accounts, SyncContext syncReports)
        {
            
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Restart();

            _companiesSheetAccountIds = new List<Guid>();
            Dictionary<Guid, Dictionary<Guid, List<int>>> _processedCompanyDictionary = new Dictionary<Guid, Dictionary<Guid, List<int>>>();

            var batch = Split(ReadRows(companiesInUniversesWorksheet), 400);
            foreach(var batchItem in batch)
            {
                if(batchItem.Count > 0)
                    Console.WriteLine("processing row = {0} , {1}", batchItem[0].Item4, batchItem[batchItem.Count -1].Item4);
               
                var companiesIdsIndex = _clientDwRepository.GetCompanyIdByCiqid(batchItem.Select(b => b.Item1).ToList());
                foreach (var row in batchItem)
                {
                    UniverseCompany universeCompany = null;
                    UploadErrorLog uploadErrorLog = null;

                    var ciqid = row.Item1.ToUpperInvariant();
                    var universeName = row.Item2;
                    var accountName = row.Item3;
                    int rowNumber = row.Item4;

                    try
                    {
                        var accountId = GetAccountId(rowNumber, row.Item5, false);
                        CheckMatchBetweenAccountNameAndID(rowNumber, accountName, accountId, accounts, false);

                        Guid companyId;
                        if (!companiesIdsIndex.TryGetValue(ciqid, out companyId))
                        {
                            uploadErrorLog = new UploadErrorLog() { RowNumber = rowNumber + 1, ErrorMessage = string.Format("The company with CIQID: '{0}' could not be found.", ciqid), Worksheet = CompaniesInUniversesWorksheetName, FileId = fileId };
                            continue;
                        }

                        int? universeId = this.GetUniverseId(universeName, accountId);
                        if (universeId == null)
                        {
                            uploadErrorLog = new UploadErrorLog() { RowNumber = rowNumber + 1, ErrorMessage = string.Format("The universe with name: '{0}' could not be found for the specified account.", universeName), Worksheet = CompaniesInUniversesWorksheetName, FileId = fileId };
                            continue;
                        }

                        if (!_companiesSheetAccountIds.Contains(accountId))
                            _companiesSheetAccountIds.Add(accountId);


                        if (_processedCompanyDictionary.ContainsKey(companyId))
                            if (_processedCompanyDictionary[companyId].ContainsKey(accountId))
                                if (_processedCompanyDictionary[companyId][accountId].Contains(universeId.Value))
                                    continue;

                        universeCompany = new UniverseCompany() { CompanyId = companyId, UniverseId = universeId.Value, CIQID = ciqid, AccountId = accountId };


                        if (_processedCompanyDictionary.ContainsKey(companyId))
                        {
                            if (_processedCompanyDictionary[companyId].ContainsKey(accountId))
                            {
                                if (!_processedCompanyDictionary[companyId][accountId].Contains(universeId.Value))
                                    _processedCompanyDictionary[companyId][accountId].Add(universeId.Value);
                            }
                            else
                            {
                                _processedCompanyDictionary[companyId].Add(accountId, new List<int>() { universeId.Value });
                            }
                        }
                        else
                        {
                            var dict = new Dictionary<Guid, List<int>>();
                            dict.Add(accountId, new List<int>() { universeId.Value });

                            _processedCompanyDictionary.Add(companyId, dict);
                        }
                    }
                    catch (AccountNotFoundException ex)
                    {
                        uploadErrorLog = new UploadErrorLog() { RowNumber = ex.RowNumber + 1, ErrorMessage = ex.Message, Worksheet = CompaniesInUniversesWorksheetName, FileId = fileId };
                        continue;
                    }
                    catch (CompanyNotFoundException ex)
                    {
                        uploadErrorLog = new UploadErrorLog() { RowNumber = rowNumber + 1, ErrorMessage = ex.Message, Worksheet = CompaniesInUniversesWorksheetName, FileId = fileId };
                        continue;
                    }
                    catch (ApplicationException ex)
                    {
                        uploadErrorLog = new UploadErrorLog() { RowNumber = rowNumber + 1, ErrorMessage = ex.Message, Worksheet = CompaniesInUniversesWorksheetName, FileId = fileId };
                        syncReports.Log(ex.Message);
                        continue;
                    }
                    catch (Exception ex)
                    {
                        uploadErrorLog = new UploadErrorLog() { RowNumber = 0, ErrorMessage = string.Format("Unknown error. Please contact ICT support. Error message: {0}", ex.Message), Worksheet = CompaniesInUniversesWorksheetName, FileId = fileId };
                        continue;
                    }

                    yield return new KeyValuePair<UniverseCompany, UploadErrorLog>(universeCompany, uploadErrorLog);
                }
            }
        }

        IEnumerable<Tuple<string, string, string,int, string>> ReadRows(IWorksheet companiesInUniversesWorksheet )
        {
            for (int rowNumber = 1; /* Break condition is evaluated inside */ ; rowNumber++)
            {
                var companiesCells = companiesInUniversesWorksheet.Cells;
              
                var ciqid = companiesCells[rowNumber, (int)CompaniesInUniversesIndices.CIQID].Text.Trim().ToUpperInvariant();
                var universeName = companiesCells[rowNumber, (int)CompaniesInUniversesIndices.UniverseName].Text.Trim();
                var accountName = companiesCells[rowNumber, (int)CompaniesInUniversesIndices.AccountName].Text.Trim();
                var accountIdString = companiesCells[rowNumber, (int)CompaniesInUniversesIndices.AccountId].Text.Trim();

                //If all cells are empty end the sync process
                if (string.IsNullOrWhiteSpace(ciqid) && string.IsNullOrWhiteSpace(accountName) && string.IsNullOrWhiteSpace(universeName) && string.IsNullOrWhiteSpace(accountIdString))
                {
                    break;
                }

                yield return new Tuple<string, string, string, int, string>(ciqid, universeName, accountName, rowNumber, accountIdString);
            }
        }

        private Universe CreateUniverse(string universeOneName, Guid accountId)
        {
            return new Universe() { Name = universeOneName, Id = _universeRepository.GetNextUniverseId(), AccountId = accountId, Created = DateTime.UtcNow, LastUpdated = DateTime.UtcNow };
        }

        private AccountFeature GetAccountFeatureByString(string featureName, int rowNumber)
        {
            try
            {
                return (AccountFeature)Enum.Parse(typeof(AccountFeature), featureName);
            }
            catch (Exception)
            {
                throw new AccountFeatureNotFoundException(string.Format("The account feature: '{0}' could not be found", featureName), featureName, rowNumber + 1);
            }
        }

        private int? GetUniverseId(string universeName, Guid accountId)
        {
            if (string.IsNullOrEmpty(universeName))
                return null;
            KeyValuePair<string, Guid> key = new KeyValuePair<string, Guid>(universeName.ToLowerInvariant(), accountId);
            int? id;
            if (this._universesCache.TryGetValue(key, out id))
                return id;
            
            id = null;
            var universe = _universeRepository.GetUniverse(universeName, accountId);
            if (universe != null)
            {
              id =  universe.Id;
            }

            this._universesCache[key] = id;
            return id;
        }

        private static IEnumerable<List<T>> Split<T>(IEnumerable<T> elements, int size)
        {
            size = size <= 0 ? 100 : size;
            List<T> result = new List<T>();
            foreach (var item in elements)
            {
                if (result.Count >= size)
                {
                    yield return result;
                    result = new List<T>();
                }

                result.Add(item);
            }

            if(result.Count > 0)
                yield return result;

        }
    }
}
